
-- 学校表 - 存储学校信息
CREATE TABLE schools (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
     -- 创建时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 更新时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- 班级表 - 存储学校班级信息
CREATE TABLE classes (
    -- 主键，自增ID
    id SERIAL PRIMARY KEY,
    -- 学校ID
    school_id INTEGER NOT NULL,
    -- 班级编号，唯一标识符 (原bh字段)
    class_code VARCHAR(20) NOT NULL UNIQUE,
    -- 班级名称 (原bj/bjmc字段)
    class_name VARCHAR(100) NOT NULL,
    -- 学院名称 (原jgmc字段)
    college_name VARCHAR(100) NOT NULL,
    -- 年级名称 (原njmc字段)
    grade_name VARCHAR(10) NOT NULL,
    -- 专业代码 (原zyh字段)
    major_code VARCHAR(20) NOT NULL,
    -- 专业名称 (原zymc字段)
    major_name VARCHAR(100) NOT NULL,
    -- 专业方向 (原zyfxdm字段)
    major_direction VARCHAR(100),
    -- 培养层次 (原pyccmc字段)
    education_level VARCHAR(20) NOT NULL,
    -- 学生人数 (原xkrs字段)
    student_count VARCHAR(20) DEFAULT 0,
    -- 学年代码 (原xnm字段)
    academic_year_code VARCHAR(10) NOT NULL,
    -- 学年名称 (原xnmc字段)
    academic_year_name VARCHAR(20) NOT NULL,
    -- 学期代码 (原xqm字段)
    term_code VARCHAR(10) NOT NULL,
    -- 校区ID (原xqh_id字段)
    campus_id VARCHAR(10) NOT NULL,
    -- 校区名称 (原xqmc字段)
    campus_name VARCHAR(50) NOT NULL,
    -- 创建时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 更新时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引以提高查询性能
-- 索引说明：
-- 学年索引: 按学年筛选班级
-- 学期索引: 按学期筛选班级
-- 校区索引: 按校区筛选班级
-- 年级索引: 按年级筛选班级
-- 学院索引: 按学院筛选班级
-- 专业名称索引: 按专业名称筛选班级
-- 专业方向索引: 按专业方向筛选班级
-- 层次索引: 按层次筛选班级
-- 班级名称索引: 按班级名称筛选班级
CREATE INDEX idx_academic_year_name ON classes (academic_year_name);
CREATE INDEX idx_term_code ON classes (term_code);
CREATE INDEX idx_campus_name ON classes (campus_name);
CREATE INDEX idx_grade_name ON classes (grade_name);
CREATE INDEX idx_college_name ON classes (college_name);
CREATE INDEX idx_major_name ON classes (major_name);
CREATE INDEX idx_major_direction ON classes (major_direction);
CREATE INDEX idx_education_level ON classes (education_level);
CREATE INDEX idx_class_name ON classes (class_name);


-- 课程表 - 存储课程信息
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    class_id INTEGER NOT NULL,
    class_code VARCHAR(100) NOT NULL UNIQUE,
    course_name VARCHAR(255) NOT NULL,
    course_name_abbr VARCHAR(100),
    teacher JSONB,
    day_of_week INTEGER NOT NULL,
    dateDigitSeparator VARCHAR(100) NOT NULL,
    campus VARCHAR(100) NOT NULL,
    location JSON NOT NULL,
    section JSONB NOT NULL,
    weeks JSONB NOT NULL,
    className VARCHAR(100) NOT NULL,
    all_classes TEXT[] NOT NULL,
    courseType VARCHAR(50),
    credit VARCHAR(50),
    classHour VARCHAR(50),
    studentCount VARCHAR(50) DEFAULT 0,
    assessment VARCHAR(50),
    teachingMethod VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    bremark JSONB,
    FOREIGN KEY (class_code) REFERENCES classes(class_code) ON DELETE CASCADE
);

-- 创建触发器函数用于自动更新updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为classes表创建触发器
CREATE TRIGGER update_classes_updated_at
    BEFORE UPDATE ON classes
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 为courses表创建触发器
CREATE TRIGGER update_courses_updated_at
    BEFORE UPDATE ON courses
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 为schools表创建触发器
CREATE TRIGGER update_schools_updated_at
    BEFORE UPDATE ON schools
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 用户表 - 存储管理员账号信息
CREATE TABLE user (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 为user表创建触发器
CREATE TRIGGER update_user_updated_at
BEFORE UPDATE ON user
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 系统日志表 - 记录所有系统操作和事件
CREATE TABLE system_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    log_type VARCHAR(20) NOT NULL,  -- login/error/data_change/api_call
    action VARCHAR(50) NOT NULL,  -- 具体操作类型
    description TEXT,  -- 详细描述
    table_name VARCHAR(50),  -- 相关表名(如果是数据操作)
    record_id INTEGER,  -- 相关记录ID
    old_value JSONB,  -- 旧值(修改前)
    new_value JSONB,  -- 新值(修改后)
    ip_address VARCHAR(50),
    user_agent VARCHAR(255),
    status_code INTEGER,  -- HTTP状态码或错误码
    request_data JSONB,  -- 请求数据
    response_data JSONB,  -- 响应数据
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 为表字段添加注释（PostgreSQL语法）
COMMENT ON TABLE users IS '系统用户表';
COMMENT ON COLUMN users.username IS '登录用户名';
COMMENT ON COLUMN users.password_hash IS '密码哈希值';
COMMENT ON COLUMN users.role IS '用户角色';

COMMENT ON TABLE operation_logs IS '操作日志表';
COMMENT ON COLUMN operation_logs.user_id IS '操作用户ID';
COMMENT ON COLUMN operation_logs.action IS '操作类型(create/update/delete)';
COMMENT ON COLUMN operation_logs.table_name IS '操作的表名';
COMMENT ON COLUMN operation_logs.record_id IS '记录的ID';
COMMENT ON COLUMN operation_logs.old_value IS '旧值(修改前)';
COMMENT ON COLUMN operation_logs.new_value IS '新值(修改后)';

COMMENT ON TABLE schools IS '学校信息表';
COMMENT ON COLUMN schools.id IS '主键，自增ID';
COMMENT ON COLUMN schools.name IS '学校名称';
COMMENT ON COLUMN schools.created_at IS '记录创建时间';
COMMENT ON COLUMN schools.updated_at IS '记录最后更新时间';

COMMENT ON TABLE classes IS '班级信息表';
COMMENT ON COLUMN classes.id IS '主键，自增ID';
COMMENT ON COLUMN classes.class_code IS '班级编号，如23371101';
COMMENT ON COLUMN classes.class_name IS '班级完整名称，如"智慧建筑与建造2301"';
COMMENT ON COLUMN classes.college_name IS '所属学院名称，如"电力与建筑学院"';
COMMENT ON COLUMN classes.grade_name IS '年级名称，如2023';
COMMENT ON COLUMN classes.major_code IS '专业代码，如3711';
COMMENT ON COLUMN classes.major_name IS '专业名称，如"智慧建筑与建造"';
COMMENT ON COLUMN classes.major_direction IS '专业方向，如"建筑设计"';
COMMENT ON COLUMN classes.education_level IS '培养层次，如"本科"';
COMMENT ON COLUMN classes.student_count IS '班级学生人数';
COMMENT ON COLUMN classes.academic_year_code IS '学年代码，如2025';
COMMENT ON COLUMN classes.academic_year_name IS '学年名称，如"2025-2026"';
COMMENT ON COLUMN classes.term_code IS '学期代码，如3';
COMMENT ON COLUMN classes.campus_id IS '校区ID，如4';
COMMENT ON COLUMN classes.campus_name IS '校区名称，如"东山校区"';
COMMENT ON COLUMN classes.created_at IS '记录创建时间';
COMMENT ON COLUMN classes.updated_at IS '记录最后更新时间';

COMMENT ON TABLE courses IS '课程信息表';
COMMENT ON COLUMN courses.id IS '主键，自增ID';
COMMENT ON COLUMN courses.class_code IS '班级编号，如23371101';
COMMENT ON COLUMN courses.course_name IS '课程名称';
COMMENT ON COLUMN courses.course_name_abbr IS '课程名称缩写';
COMMENT ON COLUMN courses.teacher IS '授课教师信息，使用JSON格式存储';
COMMENT ON COLUMN courses.day_of_week IS '上课星期几，1-7表示周一到周日';
COMMENT ON COLUMN courses.dateDigitSeparator IS '上课日期，2025-9-27表示2025年9月27日';
COMMENT ON COLUMN courses.campus IS '校区名称';
COMMENT ON COLUMN courses.location IS '上课地点';
COMMENT ON COLUMN courses.section IS '课程节次信息，使用JSON格式存储';
COMMENT ON COLUMN courses.weeks IS '上课周次，使用JSON格式存储';
COMMENT ON COLUMN courses.className IS '班级名称，如"软件工程2313"';
COMMENT ON COLUMN courses.all_classes IS '所有班级名称，如"软件工程2313;软件工程2314"';
COMMENT ON COLUMN courses.courseType IS '课程类型，如"必修"';
COMMENT ON COLUMN courses.credit IS '学分，如3.0';
COMMENT ON COLUMN courses.classHour IS '学时，如2';
COMMENT ON COLUMN courses.studentCount IS '学生人数';
COMMENT ON COLUMN courses.assessment IS '考核方式，如"考试"';
COMMENT ON COLUMN courses.teachingMethod IS '授课方式，如"面授"';
COMMENT ON COLUMN courses.created_at IS '记录创建时间';
COMMENT ON COLUMN courses.updated_at IS '记录最后更新时间';
COMMENT ON COLUMN courses.bremark IS '备注';
